import requests
from bs4 import BeautifulSoup
import pymysql

def fetch_html():
    url = 'https://cd.esf.fang.com/house/i31/'
    response = requests.get(url)
    parse_html(response.text)


def parse_html(html):
    soup = BeautifulSoup(html, 'html.parser')
    dl_list = soup.select(".shop_list dl")
    result = []
    for dl in dl_list:
        detail = {}
        img = (dl.dt.a.img.attrs['data-src'])
        tit_shop = dl.select('.tit_shop')[0].text
        price = dl.select('.price_right .red')[0].text
        single_price = dl.select('.price_right > span:nth-child(2)')[0].text
        detail['img'] = img
        detail['tit_shop'] = tit_shop
        detail['price'] = price
        detail['single_price'] = single_price
        result.append(detail)
    save(result)


def save(data):
    for item in data:
        insert(item['img'], item['tit_shop'], item['price'], item['single_price'])


def init_db():
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        database='house',
    )
    c = conn.cursor()
    sql = '''
            CREATE TABLE house (
            id INT PRIMARY KEY AUTO_INCREMENT,
            img TEXT NOT NULL,
            tit_shop TEXT NOT NULL,
            price TEXT NOT NULL,
            single_price TEXT NOT NULL
            );
          '''
    c.execute(sql)
    conn.commit()
    conn.close()


def insert(img, tit_shop, price, singe_price):
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        database='house',
    )
    cur = conn.cursor()
    sql = '''insert into house(img, tit_shop, price, single_price)values ('%s', '%s', '%s', '%s') ''' % (
        img, tit_shop, price, singe_price)
    cur.execute(sql)
    conn.commit()
    conn.close()


if __name__ == '__main__':
    init_db()
    fetch_html()
